<!DOCTYPE html>
<html>
    <head>
        <title>CS143 Project 1B</title>
    </head>
    <body>
        <h1>CS143 Project 1B</h1>
        (04/21/2012 by Jessica Kain)
        <br>

        <p> Type an SQL query in the following box: </p>
        <p> </p>
        <!--<form method="GET" action=".">-->
        <form method="GET">
        <textarea rows="8" cols="60" name="in_query"></textarea>

        <!-- The following did not work:
        //echo htmlspecialchars($_GET["in_query"]);
        //echo $_GET["in_query"];
        -->
        
        <input type="submit" value="Submit">
        </form>
        <p></p>
        
        <p>
        <small>Note: tables and fields are case sensitive. Run "show tables" to 
            see the list of available tables.</small>
        </p>
        
        <?php
        
        // Retrieve the inputted query and load into a variable.
        $query = $_GET["in_query"];
        
        if($query)
        {
            // First, I need to establish a connection with the database as
            // follows:
            $db_connection = mysql_connect("localhost", "cs143", "");
            
            // If unable to connect to the database, return an error and exit.
            if(!$db_connection)
            {
                $errmsg = mysql_error($db_connection);
                echo "Connection failed: ".$errmsg;
                exit(1);
            }
            
            // After the connection is established, I next need to select the
            // desired database I wish to work in.
            //mysql_select_db("TEST", $db_connection);
            mysql_select_db("CS143", $db_connection);
            
            // In the instructions, it says that we may assume that users will 
            // always issue correct SELECT queries and all user inputs can be 
            // trusted. However, on Google Groups, the TA said that this is not
            // done in practice and that we should escape by using the escape
            // function. Later, after talking with the TA in person, he said
            // that using the escape function for this particular assignment
            // was not necessary. He later sent out an email saying:
            // "Given the fact that in P1B we grade by typing the entire SQL,
            // mysql_real_escape_string does not fit this case." The comments
            // below were my attempt at using the escape function. I later
            // commented this code out.
            
            //$sanitized_query = mysql_real_escape_string($query, $db_connection);
            //$result = mysql_query($sanitized_query, $db_connection);
                        
            // Read the data of the query:
            $result = mysql_query($query, $db_connection);

            // Before I can create a table, I need to count the fields (aka
            // columns in the relation:
            $column_num = mysql_num_fields($result);
                    
            
            echo "<h2>Results from MySQL:</h2>";
            echo "<table cellspacing='1' cellpadding='2' border='1'>";
            echo  "<tr align='center'>";
            for($i=0; $i < $column_num; $i++)
            {
                // Get the headers for the relation:
                $header = mysql_fetch_field($result, $i);
                echo "<th>$header->name</th>";
            }
            echo "</tr>";
                      
            while($row = mysql_fetch_row($result))
            {
                echo  "<tr align='center'>";
                for($j=0; $j < $column_num; $j++)
                {
                    if (is_null($row[$j]))
                        $row[$j] = 'N/A';
                    echo "<td>" . $row[$j] . "</td>";
                }
                echo "</tr>"; 
            }
            
            echo "</table>";
            
            
            // After the inputted query is processed, I need to close the
            // connection or else I will get connection errors!
            mysql_close($db_connection); 
        } 
        ?>
        
        
    </body>
</html>
